package com.jiazhong.basic.pack0524.onetomany.dao;

import com.jiazhong.basic.pack0524.db.DBManager;
import com.jiazhong.basic.pack0524.onetomany.bean.Dept;
import com.jiazhong.basic.pack0524.onetomany.bean.Emp;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashSet;
import java.util.Set;

public class DeptDAO {

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @SneakyThrows
    public Dept findById1(int deptNo) {
        conn = DBManager.getConnection();
        String sql = "select * from dept where deptno=?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, deptNo);
        rs = ps.executeQuery();
        if (rs.next()) {
            Dept dept = new Dept();
            dept.setDeptNo(rs.getInt(1));
            dept.setDname(rs.getString(2));
            dept.setLoc(rs.getString(3));
            return dept;
        }
        return null;
    }

    @SneakyThrows
    public Dept findById2(int deptNo) {
        Dept dept = new Dept();
        Set<Emp> set = new HashSet<>();
        conn = DBManager.getConnection();
        String sql = "select * from dept d left join emp e on d.deptno = e.deptno where state=1 and d.deptno=?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, deptNo);
        rs = ps.executeQuery();
        while (rs.next()) {
            // 部门信息
            dept.setDeptNo(rs.getInt(1));
            dept.setDname(rs.getString(2));
            dept.setLoc(rs.getString(3));
            // 员工信息
            Emp emp = new Emp();
            emp.setEmpNo(rs.getInt(4));
            emp.setEname(rs.getString(5));
            emp.setJob(rs.getString(6));
            emp.setMgr(rs.getInt(7));
            emp.setHireDate(rs.getString(8));
            emp.setSal(rs.getDouble(9));
            emp.setComm(rs.getDouble(10));
            emp.setDeptNo(rs.getInt(11));
            emp.setState(rs.getInt(12));
            set.add(emp);
        }
        dept.setEmps(set);
        return dept;
    }
}
